{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "6dd61309",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd,xlwings as xw"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea20dffa",
   "metadata": {},
   "source": [
    "# 原始数据导入与预处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "35b5893a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 原始数据导入与预处理\n",
    "wbook1= xw.Book(r\"j:\\王振洋资料\\1.商贸公司资料\\9月商贸公司资料\\本月采购\\9.14DG24091400164袁艳芳中睿源.xls\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "54eae2ed",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 修改明细表中的标题和 这里的字段一致\n",
    "df1=wbook1.sheets('明细').range(\"a1\").expand().options(pd.DataFrame).value  #从明细表的a1开始\n",
    "df2=df1.groupby(by=[\"城市名称\",\"商品名称\"])[[\"数量\",\"金额\"]].sum()\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "ce33e751",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>数量</th>\n",
       "      <th>金额</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>城市名称</th>\n",
       "      <th>商品名称</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">南宁市</th>\n",
       "      <th>蛋糕保温袋</th>\n",
       "      <td>160.0</td>\n",
       "      <td>4480.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>防晒冰袖</th>\n",
       "      <td>50.0</td>\n",
       "      <td>175.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>合肥市</th>\n",
       "      <th>蛋糕保温袋</th>\n",
       "      <td>15.0</td>\n",
       "      <td>420.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>唐山市</th>\n",
       "      <th>防晒冰袖</th>\n",
       "      <td>50.0</td>\n",
       "      <td>175.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>天津市</th>\n",
       "      <th>防晒冰袖</th>\n",
       "      <td>50.0</td>\n",
       "      <td>175.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               数量      金额\n",
       "城市名称 商品名称                \n",
       "南宁市  蛋糕保温袋  160.0  4480.0\n",
       "     防晒冰袖    50.0   175.0\n",
       "合肥市  蛋糕保温袋   15.0   420.0\n",
       "唐山市  防晒冰袖    50.0   175.0\n",
       "天津市  防晒冰袖    50.0   175.0"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "df2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 编码匹配中间表"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 匹配仓库、部门、客户、存货编码、科目 等 各项内容\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 从套装拆件中间表读取数据\n",
    "\n",
    "df3=wbook1.sheets('明细').range('a1').expand().options(pd.DataFrame,index=False).value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# 添加一个城市2列\n",
    "df3['城市2']=df3['城市名称'].str.replace(\"市\",\"\") #去掉城市名称中的“市”字，用于部门和仓库匹配\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 打开t+基础数据表并写入相关数据，为匹配做准备\n",
    "wb基础数据=xw.Book(r'J:\\王振洋资料\\1.商贸公司资料\\t+基础数据-商贸.xlsx')\n",
    "仓库档案=wb基础数据.sheets('仓库档案').range('a1').expand().options(pd.DataFrame,index=False).value\n",
    "部门档案=wb基础数据.sheets('部门档案').range('a1').expand().options(pd.DataFrame,index=False).value\n",
    "存货档案=wb基础数据.sheets('存货档案').range('a1').expand().options(pd.DataFrame,index=False).value\n",
    "存货档案=存货档案.loc[:,['存货编码','存货名称','计量单位','收入科目编码','收入科目名称']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 匹配 存货编码、仓库编码、部门编码等\n",
    "合并=pd.merge(df3,仓库档案,left_on='城市2',right_on='仓库名称',how='left')\n",
    "合并=pd.merge(合并,部门档案,left_on='城市2',right_on='部门',how='left')\n",
    "合并=pd.merge(合并,存货档案,left_on='商品名称',right_on='存货名称',how='left')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d8932541",
   "metadata": {},
   "source": [
    "## 将数据写入到编码匹配中间表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "3858e303",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 将合并写入<编码匹配中间表>\n",
    "\n",
    "# 创建一个sheet名字构建的列表，用于后续判断\n",
    "sheet_name=[wbook1.sheets[i].name for i in range(wbook1.sheets.count)]\n",
    "# 将数据写入到编码匹配中间表，对匹配失败的数据手动补充\n",
    "if '编码匹配中间表' in sheet_name:\n",
    "    ws=wbook1.sheets('编码匹配中间表')\n",
    "    ws.cells.clear()\n",
    "    ws.cells.number_format = '@'\n",
    "    ws.range('a1').value=合并\n",
    "else:\n",
    "    new_sheet=wbook1.sheets.add('编码匹配中间表')\n",
    "    new_sheet.cells.number_format = '@'\n",
    "    new_sheet.range('a1').value=合并\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "90455bcc",
   "metadata": {},
   "source": [
    "# 重新按仓库/部门/存货 压缩数据 并创建备注；添加供应商编码、供应商"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "29763780",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 压缩数据\n",
    "# 将读取后的数据 进行压缩、汇总、透视，并生成备注列\n",
    "df1=wbook1.sheets('编码匹配中间表').range('a1').expand().options(pd.DataFrame).value\n",
    "\n",
    "\n",
    "df2=df1.groupby(by=['城市2','部门编码','仓库编码','存货编码','存货名称','计量单位','供应商编码','供应商'])[['数量','金额']].sum().reset_index()\n",
    "df2['数量2']=df2['数量'].astype(str).str.replace('.0','')  # 新增一个数量2列，转化为文本并替换其中的.0，方便后续做文本链接\n",
    "df2['备注'] = df2['城市2'] + '购入'  + df2['数量2'] + df2['存货名称']  #增加一个备注列\n",
    "df2=df2.drop('数量2',axis=1)     #删除数量2列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "e526c648",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 添加汇总备注的函数，有了这个之后，备注可以按城市共用一个.\n",
    "def 添加汇总备注(x):\n",
    "    x['备注2'] = '+'.join(x['备注'].values)\n",
    "    x=x.drop('备注',axis=1)\n",
    "    return x\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "68a27ac1",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "df3=df2.groupby(by=['城市2']).apply(添加汇总备注)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3321b247",
   "metadata": {},
   "source": [
    "# 模版数据源表\n",
    "在编码匹配中间表补充好数据的编码、科目等数据信息等后，就可以将数据粘贴到模版数据源表，并进一步补充“不含税金额”，“税额”，“往来单位等”。补充完成后，以模版数据源为基础，生成销售出库单和凭证导入导出表中的数据\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "3e47f7a0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 将合并写入<编码匹配中间表>\n",
    "\n",
    "# 创建一个sheet名字构建的列表，用于后续判断\n",
    "sheet_name=[wbook1.sheets[i].name for i in range(wbook1.sheets.count)]\n",
    "# 将数据写入到编码匹配中间表，对匹配失败的数据手动补充\n",
    "if '模版数据源表' in sheet_name:\n",
    "    ws=wbook1.sheets('模版数据源表')\n",
    "    ws.cells.clear()   #清楚表里现有的数据\n",
    "    ws.cells.number_format = '@'\n",
    "    ws.range('a1').value=df3.reset_index(drop=True)\n",
    "else:\n",
    "    new_sheet=wbook1.sheets.add('模版数据源表')\n",
    "    new_sheet.cells.number_format = '@'\n",
    "    new_sheet.range('a1').value=df3.reset_index(drop=True)\n",
    "    \n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据写入到采购入库单"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "3d81f758",
   "metadata": {},
   "outputs": [],
   "source": [
    "wb_采购入库单=xw.Book(r'J:\\王振洋资料\\1.商贸公司资料\\0.采购入库单模版-商贸.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "3664fc73",
   "metadata": {},
   "outputs": [],
   "source": [
    "ws2=wbook1.sheets('采购入库单')\n",
    "ws3=wbook1.sheets('模版数据源表')\n",
    "df4=ws3.range('a1').expand().options(pd.DataFrame).value\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "67051f2c",
   "metadata": {},
   "outputs": [],
   "source": [
    "ws2.range('a5:aa10000').clear_contents()\n",
    "ws2.range('g3:i3').value=df4.loc[:,['供应商编码','供应商','部门编码','城市2']].values\n",
    "ws2.range('m3:o3').value=df4.loc[:,['备注2','仓库编码','城市2']].values\n",
    "ws2.range('r3:s3').value=df4.loc[:,['存货编码','存货名称']].values\n",
    "ws2.range('u3:v3').value=df4.loc[:,['计量单位','数量']].values\n",
    "ws2.range('y3').options(transpose=True).value=df4['金额'].values\n",
    "ws2.range('z3').value=0.13\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
